#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive	

${HIVE_HOME} -S -e "

INSERT INTO hive.online_edu_dwb.dwb_apply_wide_table
SELECT
    fcr.id AS id,
    fcr.customer_id AS customer_id,
    fcr.create_date_time AS create_date_time,
    fcr.update_date_time AS update_date_time,
    fcr.deleted AS deleted,
    fcr.payment_state AS payment_state,
    fcr.payment_time AS payment_time,
    fcr.course_id AS course_id,
    fcr.course_name AS course_name,
    fcr.total_fee AS total_fee,
    fcr.origin_type AS origin_type,
    fcr.origin_channel AS origin_channel,
    fcr.creator AS creator,
    fcr.creator_name AS creator_name,
    sch.id AS itcast_school_id,
    sch.name AS itcast_school_name,
    subj.id AS itcast_subject_id,
    subj.name AS itcast_subject_name,
    dep.id AS tdepart_id,
    dep.name AS tdepart_name,
    appl.appeal_status AS appeal_status,
    SUBSTRING (fcr.payment_time, 1 ,10) AS dt --动态分区
FROM
-- 第一个表:先对fact_customer_relationship的拉链过过滤,提高join的效率
(SELECT * FROM hive.online_edu_dwd.fact_customer_relationship WHERE end_date = '9999-99-99') fcr
--左关联第二个表(学校表)
LEFT JOIN hive.online_edu_dwd.dim_itcast_school sch
ON fcr.itcast_school_id = sch.id AND sch.end_date = '9999-99-99'
--左关联第三个表(学科表)
LEFT JOIN hive.online_edu_dwd.dim_itcast_subject subj
ON fcr.itcast_subject_id = subj.id AND subj.end_date = '9999-99-99'
--左关联第四个表(学员工表)
LEFT JOIN hive.online_edu_dwd.dim_employee emp
ON fcr.creator = emp.id AND emp.end_date = '9999-99-99'
--左关联第五个表(销售部门表)
LEFT JOIN hive.online_edu_dwd.dim_scrm_department dep
ON emp.tdepart_id = dep.id AND dep.end_date = '9999-99-99'
--左关联第六个表(线索申诉信息表)
LEFT JOIN hive.online_edu_dwd.dim_customer_appeal appl
on fcr.id = appl.customer_relationship_first_id AND appl.end_date = '9999-99-99';